package nutella89.basic;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
//ctrl + shift + o 를 누르면 자동으로 연결
import java.util.ArrayList;

//import java.sql.*;

public class J22_BookDBDAO implements J22_BookDBService{
	
	// JDBC 접속정보
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";
	
	// SQL문 정의
	private static final String insertSQL = " insert into bookdb(id,title,publisher,author) "
			+ " values(bookid.nextval,?,?,?) ";
	private static final String selectSQL = " select id,title from bookdb order by id desc ";
	private static final String selectoneSQL = " select * from bookdb where id=? ";
	private static final String updateSQL = " update bookdb set title=?,publisher=?,author=?, "
			+ " regdate=sysdate where id=? ";
	private static final String deleteSQL = " delete from bookdb where id=? ";
	
	
	@Override
	public int insertBookDB(J22_BookDBVO bvo) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(insertSQL);
				ps.setString(1, bvo.getTitle());
				ps.setString(2, bvo.getPublisher());
				ps.setString(3, bvo.getAuthor());
			cnt = ps.executeUpdate();
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(ps!=null)
				try{ps.close();}catch(Exception e){}
			if(conn!=null)
				try{conn.close();}catch(Exception e){}
		}
		
		return cnt;
	}

	@Override
	public ArrayList<J22_BookDBVO> selectBookDB() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;		// 추가됨!
		ArrayList<J22_BookDBVO> data = new ArrayList();	// 변경됨!!
		
		try{
			Class.forName(DRV);
			conn= DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			
			// select sql 실행 후 결과 처리 - 컬렉션 사용
			while(rs.next()){
				J22_BookDBVO b = new J22_BookDBVO(rs.getString(2),"","");
				b.setId(rs.getString(1));
				
				data.add(b);
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(rs!=null)
				try{rs.close();}catch(Exception e){}
			if(ps!=null)
				try{ps.close();}catch(Exception e){}
			if(conn!=null)
				try{conn.close();}catch(Exception e){}
		}
		
		return data;
	}

	@Override
	public J22_BookDBVO selectBookDB(String id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		J22_BookDBVO b = null;	// 추가됨!@!
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectoneSQL);
				ps.setString(1, id);
			rs = ps.executeQuery();
			
			while(rs.next()){
				b = new J22_BookDBVO(rs.getString(2), rs.getString(3), rs.getString(4));
				b.setId(rs.getString(1));
				b.setRegdate(rs.getString(5));
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(rs!=null)
				try{rs.close();}catch(Exception e){}
			if(ps!=null)
				try{ps.close();}catch(Exception e){}
			if(conn!=null)
				try{conn.close();}catch(Exception e){}
		}
		
		return b;
	}

	@Override
	public int updateBookDB(J22_BookDBVO bvo) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(updateSQL);
				ps.setString(1, bvo.getTitle());
				ps.setString(2, bvo.getPublisher());
				ps.setString(3, bvo.getAuthor());
				ps.setString(4, bvo.getId());
			cnt = ps.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(ps!=null)
				try{ps.close();}catch(Exception e){}
			if(conn!=null)
				try{conn.close();}catch(Exception e){}
		}
		
		return cnt;
	}

	@Override
	public int deleteBookDB(String id) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(deleteSQL);
				ps.setString(1, id);
			cnt = ps.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(ps!=null)
				try{ps.close();}catch(Exception e){}
			if(conn!=null)
				try{conn.close();}catch(Exception e){}
		}
		
		return cnt;
	}
	
}
